from flask import Flask, render_template, request, send_file
import pandas as pd
import re
import os
from io import BytesIO

app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['ALLOWED_EXTENSIONS'] = {'xlsx', 'xls'}

# 确保上传目录存在
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in app.config['ALLOWED_EXTENSIONS']

def extract_number(text):
    match = re.search(r'[\d.]+', text)
    return float(match.group()) if match else None

def process_data(file_path):
    # 读取并合并所有工作表
    with pd.ExcelFile(file_path) as excel_file:  # ✅ 关键修改
        sheet_names = excel_file.sheet_names
        df_combined = pd.DataFrame()

        for sheet in sheet_names:
            df = pd.read_excel(excel_file, skiprows=1, sheet_name=sheet)
            df_combined = pd.concat([df_combined, df], ignore_index=True)

    # 过滤目标货品
    target_products = [
        '菠菜（份）', '香菜（份）', '生菜（份）','茼蒿（份）','青菜拼盘（份）','鲜菌拼盘（份）','金针菇（份）',
        '平菇（份）', '土豆（份）','宽粉（斤）', '铁杆山药（份）','荔浦芋头（份）','娃娃菜（份）','春笋（份）','莲藕（份）',
        '香菜末', '香葱末', '香葱段','姜片','大白菜（斤）','蒜米（斤)','小米辣段','青萝卜（斤）','白菜叶（份）','白菜芯（份）',
        '鲜牛肉',  '羔羊大串', '羔羊肉串', '羊鞭（串）','羊腰子（串）', '草原四宝（份）', '羊肝（熟）', '羊肚（份）',
        '手工面（份）', '面团', '麻汁','翅中（蜜汁味）','翅中（新奥尔良味）', '糖醋白萝卜（泡菜）','牛骨髓（份）', '小吊梨汤（份）',
        '羊肉水饺（份）','牛肉水饺（份）','手工牛肉丸','手工羊肉丸',
        '香菇牛肉酱', '香辣牛肉酱', '炒芝麻','咖喱牛肉酱','海鲜汁', '沙茶酱', '烧烤油'
    ]
    filtered_df = df_combined[df_combined['货品名称'].isin(target_products)]

    # 计算剩余库存
    filtered_df['库存数量(单位)'] = filtered_df['库存数量(单位)'].apply(extract_number)
    filtered_df['合计'] = pd.to_numeric(filtered_df['合计'], errors='coerce')
    filtered_df['剩余库存1'] = filtered_df['库存数量(单位)'] - filtered_df['合计']
    filtered_df['剩余库存'] = filtered_df['剩余库存1'].apply(lambda x: max(x, 0))

    # 定义目标库存和补货逻辑
    target_stock = {
        '菠菜（份）': 40, '香菜（份）': 18, '生菜（份）': 28, '茼蒿（份）':46,'青菜拼盘（份）': 70, '鲜菌拼盘（份）': 40,
                '金针菇（份）': 70, '香菇（份）': 14, '平菇（份）': 18, '土豆（份）': 46, '铁杆山药（份）': 18,
                '荔浦芋头（份）':16,'娃娃菜（份）': 70,'笨菠菜（份）': 22,'白菜叶（份）':22, '白菜芯（份）': 18,
                '鲜牛肉': 50, '鲜羊肉（斤）': 25, '蒜米（斤)': 20, '小米辣段': 6,
                '香菜末': 6, '香葱末':15, '香葱段': 8, '大白菜（斤）': 30, '手工面（份）': 100, '面团': 50,'麻汁':156,
                '翅中（蜜汁味）':200, '翅中（新奥尔良味）': 200, '糖醋白萝卜（泡菜）': 40, '牛骨髓（份）': 100, '手工面（份）': 100,
        # ...（完整目标库存字典，与原始代码一致）
    }

    # 补货量计算逻辑
    def calculate_replenishment(row):
        product_name = row['货品名称']
        current_stock = row['剩余库存']
        replenishment = 0  # 初始化变量

        # 手工面特殊逻辑
        if product_name == '手工面（份）':
            if current_stock < 25:
                replenishment = 100
            elif 25 <= current_stock < 50:
                replenishment = 75
            else:
                return "不需要加工"

        # 其他特殊产品逻辑
        elif product_name == '小吊梨汤（份）':
            replenishment = 20 - current_stock if current_stock < 10 else 0

        elif product_name == '面团':
            replenishment = 70 if current_stock < 50 else 0

        elif product_name == '蒜米（斤)':
            replenishment = 20 if current_stock < 15 else 0

        # 默认逻辑（根据目标库存计算）
        else:
            target = target_stock.get(product_name, 0)
            replenishment = max(target - current_stock, 0) if not pd.isnull(current_stock) else target

        # 统一返回逻辑
        if replenishment > 0:
            return replenishment
        else:
            return "不需要加工" if replenishment == 0 else 0
        # ...（与原始代码完全一致的补货逻辑）
        return replenishment

    filtered_df['补货量'] = filtered_df.apply(calculate_replenishment, axis=1)

    # 生成内存中的 Excel 文件
    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        filtered_df.to_excel(writer, sheet_name='处理后的数据', index=False)
        df_combined.to_excel(writer, sheet_name='原始数据', index=False)
    output.seek(0)
    return output


@app.route('/', methods=['GET', 'POST'])
def index():
    filename = None  # 默认没有文件名
    if request.method == 'POST':
        if 'file' not in request.files:
            return "未选择文件"
        file = request.files['file']
        if file.filename == '':
            return "未选择文件"

        if file and allowed_file(file.filename):
            filename = file.filename  # 获取文件名
            filename = os.path.join(app.config['UPLOAD_FOLDER'], file.filename)
            file.save(filename)

            try:
                output = process_data(filename)
                return send_file(
                    output,
                    download_name="加工计划_结果.xlsx",
                    as_attachment=True,
                    mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
                )
            except Exception as e:
                return f"处理失败: {str(e)}"
            finally:
                # 带重试的文件删除
                import time
                max_retries = 3
                for attempt in range(max_retries):
                    try:
                        os.remove(filename)
                        break
                    except PermissionError:
                        if attempt < max_retries - 1:  # 最后一次尝试不等待
                            time.sleep(0.5)
                else:
                    app.logger.error(f"无法删除文件 {filename}")

    return render_template('index.html', filename=filename)


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)